{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order_id（主键）</th>\n",
       "      <th>City_id</th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchandise</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>15442</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>7</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10001</td>\n",
       "      <td>15442</td>\n",
       "      <td>早餐面包</td>\n",
       "      <td>12</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10001</td>\n",
       "      <td>22009</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>40</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10002</td>\n",
       "      <td>39502</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10002</td>\n",
       "      <td>39502</td>\n",
       "      <td>早餐面包</td>\n",
       "      <td>11</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order_id（主键）  City_id  User_id Merchandise  Quantity  Amount\n",
       "0             1    10001    15442       土鸡蛋4枚         7      28\n",
       "1             2    10001    15442        早餐面包        12      24\n",
       "2             3    10001    22009       土鸡蛋4枚        40     160\n",
       "3             4    10002    39502       土鸡蛋4枚         4      16\n",
       "4             5    10002    39502        早餐面包        11      22"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 取出所有消费总额超过 50 元的用户的所有订单号order_id\n",
    "# 读取数据集\n",
    "df1 = pd.read_excel('./示例文件.xlsx',sheet_name='data1')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    15442\n",
       "1    22009\n",
       "Name: User_id, dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 求出符合要求的用户清单\n",
    "df2 = df1.groupby(['User_id'])['Amount'].sum().reset_index()\n",
    "df2 = df2[df2['Amount'] > 50]['User_id']\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order_id（主键）</th>\n",
       "      <th>City_id</th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchandise</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>15442</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>7</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10001</td>\n",
       "      <td>15442</td>\n",
       "      <td>早餐面包</td>\n",
       "      <td>12</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10001</td>\n",
       "      <td>22009</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>40</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order_id（主键）  City_id  User_id Merchandise  Quantity  Amount\n",
       "0             1    10001    15442       土鸡蛋4枚         7      28\n",
       "1             2    10001    15442        早餐面包        12      24\n",
       "2             3    10001    22009       土鸡蛋4枚        40     160"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 合并表\n",
    "df_merge = pd.merge(df1,df2,on='User_id',how='inner')\n",
    "df_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1\n",
       "1    2\n",
       "2    3\n",
       "Name: Order_id（主键）, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 求出符合要求的订单号\n",
    "order_df = df_merge['Order_id（主键）'].drop_duplicates()\n",
    "order_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Group_id（主键）</th>\n",
       "      <th>City_id</th>\n",
       "      <th>Groupon_date</th>\n",
       "      <th>Group_type</th>\n",
       "      <th>Start_time</th>\n",
       "      <th>End_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>团购</td>\n",
       "      <td>2020-11-11 20:00:00</td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>秒杀</td>\n",
       "      <td>2020-11-12 10:00:00</td>\n",
       "      <td>2020-11-12 10:59:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>秒杀</td>\n",
       "      <td>2020-11-12 14:00:00</td>\n",
       "      <td>2020-11-12 14:59:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>团购</td>\n",
       "      <td>2020-11-11 21:00:00</td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>秒杀</td>\n",
       "      <td>2020-11-11 22:00:00</td>\n",
       "      <td>2020-11-12 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Group_id（主键）  City_id Groupon_date Group_type          Start_time  \\\n",
       "0             1    10001   2020-11-11         团购 2020-11-11 20:00:00   \n",
       "1             2    10001   2020-11-12         秒杀 2020-11-12 10:00:00   \n",
       "2             3    10001   2020-11-12         秒杀 2020-11-12 14:00:00   \n",
       "3             4    10002   2020-11-11         团购 2020-11-11 21:00:00   \n",
       "4             5    10002   2020-11-11         秒杀 2020-11-11 22:00:00   \n",
       "\n",
       "             End_time  \n",
       "0 2020-11-12 19:58:00  \n",
       "1 2020-11-12 10:59:59  \n",
       "2 2020-11-12 14:59:59  \n",
       "3 2020-11-12 20:59:00  \n",
       "4 2020-11-12 00:00:00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每个城市售卖商品分普通团购和秒杀两种方式，各自均有购买时间限制，秒杀都会放在一个普通团购时间区间内\n",
    "# 将所有订单（Order_id）所属的普通团购时间（Groupon_date）查询出来（秒杀时间对应到普通团购日期）\n",
    "# 读取数据集\n",
    "df1 = pd.read_excel('./示例文件.xlsx',sheet_name='data2_1')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order_id（主键）</th>\n",
       "      <th>City_id</th>\n",
       "      <th>Group_id</th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchandise</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>（外键）</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>2</td>\n",
       "      <td>15442.0</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>1</td>\n",
       "      <td>15442.0</td>\n",
       "      <td>早餐面包</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>3</td>\n",
       "      <td>22009.0</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>5</td>\n",
       "      <td>39502.0</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>4</td>\n",
       "      <td>39502.0</td>\n",
       "      <td>早餐面包</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order_id（主键）  City_id Group_id  User_id Merchandise\n",
       "0           NaN      NaN     （外键）      NaN         NaN\n",
       "1           1.0  10001.0        2  15442.0       土鸡蛋4枚\n",
       "2           2.0  10001.0        1  15442.0        早餐面包\n",
       "3           3.0  10001.0        3  22009.0       土鸡蛋4枚\n",
       "4           4.0  10002.0        5  39502.0       土鸡蛋4枚\n",
       "5           5.0  10002.0        4  39502.0        早餐面包"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel('./示例文件.xlsx',sheet_name='data2_2')\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order_id（主键）</th>\n",
       "      <th>City_id_x</th>\n",
       "      <th>Group_id</th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchandise</th>\n",
       "      <th>Group_id（主键）</th>\n",
       "      <th>City_id_y</th>\n",
       "      <th>Groupon_date</th>\n",
       "      <th>Group_type</th>\n",
       "      <th>Start_time</th>\n",
       "      <th>End_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>2</td>\n",
       "      <td>15442.0</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>2</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>秒杀</td>\n",
       "      <td>2020-11-12 10:00:00</td>\n",
       "      <td>2020-11-12 10:59:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>1</td>\n",
       "      <td>15442.0</td>\n",
       "      <td>早餐面包</td>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>团购</td>\n",
       "      <td>2020-11-11 20:00:00</td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>3</td>\n",
       "      <td>22009.0</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>3</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>秒杀</td>\n",
       "      <td>2020-11-12 14:00:00</td>\n",
       "      <td>2020-11-12 14:59:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>5</td>\n",
       "      <td>39502.0</td>\n",
       "      <td>土鸡蛋4枚</td>\n",
       "      <td>5</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>秒杀</td>\n",
       "      <td>2020-11-11 22:00:00</td>\n",
       "      <td>2020-11-12 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>4</td>\n",
       "      <td>39502.0</td>\n",
       "      <td>早餐面包</td>\n",
       "      <td>4</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>团购</td>\n",
       "      <td>2020-11-11 21:00:00</td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order_id（主键）  City_id_x Group_id  User_id Merchandise  Group_id（主键）  \\\n",
       "0           1.0    10001.0        2  15442.0       土鸡蛋4枚             2   \n",
       "1           2.0    10001.0        1  15442.0        早餐面包             1   \n",
       "2           3.0    10001.0        3  22009.0       土鸡蛋4枚             3   \n",
       "3           4.0    10002.0        5  39502.0       土鸡蛋4枚             5   \n",
       "4           5.0    10002.0        4  39502.0        早餐面包             4   \n",
       "\n",
       "   City_id_y Groupon_date Group_type          Start_time            End_time  \n",
       "0      10001   2020-11-12         秒杀 2020-11-12 10:00:00 2020-11-12 10:59:59  \n",
       "1      10001   2020-11-11         团购 2020-11-11 20:00:00 2020-11-12 19:58:00  \n",
       "2      10001   2020-11-12         秒杀 2020-11-12 14:00:00 2020-11-12 14:59:59  \n",
       "3      10002   2020-11-11         秒杀 2020-11-11 22:00:00 2020-11-12 00:00:00  \n",
       "4      10002   2020-11-11         团购 2020-11-11 21:00:00 2020-11-12 20:59:00  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 合并表\n",
    "df_merge = pd.merge(df2,df1,left_on='Group_id',right_on='Group_id（主键）',how='inner')\n",
    "df_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order_id（主键）</th>\n",
       "      <th>Groupon_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-11-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2020-11-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2020-11-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order_id（主键） Groupon_date\n",
       "0             1   2020-11-12\n",
       "1             2   2020-11-11\n",
       "2             3   2020-11-12\n",
       "3             4   2020-11-11\n",
       "4             5   2020-11-11"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 条件筛选\n",
    "df = df_merge[['Order_id（主键）','Groupon_date']]\n",
    "df['Order_id（主键）'] = df['Order_id（主键）'].astype(int)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>City_id</th>\n",
       "      <th>Groupon_date</th>\n",
       "      <th>Start_time</th>\n",
       "      <th>End_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>2020-11-11 20:00:00</td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>2020-11-11 21:00:00</td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 20:00:00</td>\n",
       "      <td>2020-11-13 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 21:00:00</td>\n",
       "      <td>2020-11-13 20:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-14</td>\n",
       "      <td>2020-11-14 20:00:00</td>\n",
       "      <td>2020-11-15 19:58:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  City_id Groupon_date          Start_time            End_time\n",
       "0   1    10001   2020-11-11 2020-11-11 20:00:00 2020-11-12 19:58:00\n",
       "1   2    10002   2020-11-11 2020-11-11 21:00:00 2020-11-12 20:59:00\n",
       "2   3    10001   2020-11-12 2020-11-12 20:00:00 2020-11-13 19:58:00\n",
       "3   4    10002   2020-11-12 2020-11-12 21:00:00 2020-11-13 20:59:00\n",
       "4   5    10001   2020-11-14 2020-11-14 20:00:00 2020-11-15 19:58:00"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每个城市设置团期开始结束时间区间不会重叠\n",
    "# 查询出每个城市（City_id）每个团期（Groupon_date）上次团期结束时间和本次团期结束时间\n",
    "# 读取数据集\n",
    "df1 = pd.read_excel('./示例文件.xlsx',sheet_name='data3')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>City_id</th>\n",
       "      <th>Groupon_date</th>\n",
       "      <th>Start_time</th>\n",
       "      <th>End_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>2020-11-11 20:00:00</td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 20:00:00</td>\n",
       "      <td>2020-11-13 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-14</td>\n",
       "      <td>2020-11-14 20:00:00</td>\n",
       "      <td>2020-11-15 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>2020-11-11 21:00:00</td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 21:00:00</td>\n",
       "      <td>2020-11-13 20:59:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  City_id Groupon_date          Start_time            End_time\n",
       "0   1    10001   2020-11-11 2020-11-11 20:00:00 2020-11-12 19:58:00\n",
       "2   3    10001   2020-11-12 2020-11-12 20:00:00 2020-11-13 19:58:00\n",
       "4   5    10001   2020-11-14 2020-11-14 20:00:00 2020-11-15 19:58:00\n",
       "1   2    10002   2020-11-11 2020-11-11 21:00:00 2020-11-12 20:59:00\n",
       "3   4    10002   2020-11-12 2020-11-12 21:00:00 2020-11-13 20:59:00"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 排序\n",
    "df1 = df1.sort_values(by=['City_id','Groupon_date','Start_time'],ascending=True)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>City_id</th>\n",
       "      <th>Groupon_date</th>\n",
       "      <th>Start_time</th>\n",
       "      <th>End_time</th>\n",
       "      <th>last_End_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>2020-11-11 20:00:00</td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 20:00:00</td>\n",
       "      <td>2020-11-13 19:58:00</td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-14</td>\n",
       "      <td>2020-11-14 20:00:00</td>\n",
       "      <td>2020-11-15 19:58:00</td>\n",
       "      <td>2020-11-13 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>2020-11-11 21:00:00</td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 21:00:00</td>\n",
       "      <td>2020-11-13 20:59:00</td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  City_id Groupon_date          Start_time            End_time  \\\n",
       "0   1    10001   2020-11-11 2020-11-11 20:00:00 2020-11-12 19:58:00   \n",
       "2   3    10001   2020-11-12 2020-11-12 20:00:00 2020-11-13 19:58:00   \n",
       "4   5    10001   2020-11-14 2020-11-14 20:00:00 2020-11-15 19:58:00   \n",
       "1   2    10002   2020-11-11 2020-11-11 21:00:00 2020-11-12 20:59:00   \n",
       "3   4    10002   2020-11-12 2020-11-12 21:00:00 2020-11-13 20:59:00   \n",
       "\n",
       "        last_End_time  \n",
       "0                 NaT  \n",
       "2 2020-11-12 19:58:00  \n",
       "4 2020-11-13 19:58:00  \n",
       "1                 NaT  \n",
       "3 2020-11-12 20:59:00  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 组内移动\n",
    "df1['last_End_time'] = df1.groupby(['City_id'])['End_time'].shift(1)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City_id</th>\n",
       "      <th>Groupon_date</th>\n",
       "      <th>last_End_time</th>\n",
       "      <th>End_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td></td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 19:58:00</td>\n",
       "      <td>2020-11-13 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001</td>\n",
       "      <td>2020-11-14</td>\n",
       "      <td>2020-11-13 19:58:00</td>\n",
       "      <td>2020-11-15 19:58:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td></td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10002</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2020-11-12 20:59:00</td>\n",
       "      <td>2020-11-13 20:59:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   City_id Groupon_date        last_End_time            End_time\n",
       "0    10001   2020-11-11                      2020-11-12 19:58:00\n",
       "2    10001   2020-11-12  2020-11-12 19:58:00 2020-11-13 19:58:00\n",
       "4    10001   2020-11-14  2020-11-13 19:58:00 2020-11-15 19:58:00\n",
       "1    10002   2020-11-11                      2020-11-12 20:59:00\n",
       "3    10002   2020-11-12  2020-11-12 20:59:00 2020-11-13 20:59:00"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 选择列\n",
    "df = df1[['City_id','Groupon_date','last_End_time','End_time']]\n",
    "df = df.fillna('')\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
